package com.csii.adhoc.dataunits.service.impl;

import com.alibaba.fastjson.JSON;
import com.csii.adhoc.board.filter.FilterUnitPo;
import com.csii.adhoc.dataready.TDataCondPo;
import com.csii.adhoc.dataready.TDataFromPo;
import com.csii.adhoc.dataready.TDataNewfun;
import com.csii.adhoc.dataready.mapper.TDataCondMapper;
import com.csii.adhoc.dataready.service.TDataFromService;
import com.csii.adhoc.dataunits.*;
import com.csii.adhoc.dataunits.NumberGroup;
import com.csii.adhoc.dataunits.UnitBase.UnitBasePo;
import com.csii.adhoc.dataunits.UnitBase.UnitBaseVo;
import com.csii.adhoc.dataunits.addcolumn.*;
import com.csii.adhoc.dataunits.dao.DataSourceDao;
import com.csii.adhoc.dataunits.service.DataUnitsService;
import com.csii.adhoc.dataunits.dao.DataUnitsDao;
import com.csii.adhoc.dblink.DbLinkPo;
import com.csii.adhoc.dblink.mapper.DbLinkMapper;
import com.csii.adhoc.id.UidGenerator;
import com.csii.adhoc.util.BeanCreater;
import com.csii.adhoc.dataunits.service.DataUnitUtils;
import com.csii.adhoc.util.TreeUtils;
import com.csii.common.DataFromVo;
import com.csii.common.DbLinkUtils;
import com.csii.result.PageVo;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class DataUnitsServiceImpl implements DataUnitsService {
    private static final Logger log = LoggerFactory.getLogger(DataUnitsServiceImpl.class);

    @Autowired
    private DataSourceDao dataSourceDao;
    @Autowired
    private DataUnitsDao dataUnitsDao;
    @Autowired
    private UidGenerator uidGenerator;
    @Autowired
    private TDataCondMapper tDataCondMapper;
    @Autowired
    private DbLinkMapper dbLinkMapper;
    @Autowired
    private DbLinkUtils dbLinkUtils;
    @Autowired
    private TDataFromService tDataFromService;

    private final Object lock = new Object();


    @Override
    public List<Map<String, Object>> dataSqlQueryList(String tableId, String sql, int pageNum, int pageSize) {
        String connId = dataSourceDao.getConnIdByTableId(tableId).getConnId();
        // dataUnitsDao 查询数据库表
        //PageHelper.startPage(pageNum, pageSize);
        //List<Map<String, Object>> lists = dataUnitsDao.generalDataQuery(sql);
        PageVo<Map<String, Object>> pageSqlResult = dbLinkUtils.getPageSqlResult(tableId,connId, sql, pageNum, pageSize);
        return pageSqlResult.getRecords();
    }

    @Override
    public List<Map<String, Object>> dataSqlQueryListCamelCase(String connId, String sql, int pageNum, int pageSize) {
        List<Map<String, Object>> listQuery = dataSqlQueryList(connId, sql, pageNum, pageSize);
        List<Map<String, Object>> listReturn = new ArrayList<>();
        for (Map<String, Object> map : listQuery) {
            HashMap<String, Object> tmpHashMap = new HashMap<>();
            Set<String> keySet = map.keySet();
            for (String key : keySet) {
                String newKey = DataUnitUtils.camelSqlFieldName(key);
                tmpHashMap.put(newKey, map.get(key));
            }
            listReturn.add(tmpHashMap);
        }
        // 建议 gc 回收垃圾
        listQuery = null;
        return listReturn;
    }

    @Override
    public List<List> dataSqlQueryListObjects(String connId, String sql, int pageNum, int pageSize) {
        List<List> retList = new ArrayList<>();
        List<Map<String, Object>> list = dataSqlQueryListCamelCase(connId, sql, pageNum, pageSize);

        for (Map<String, Object> map : list) {
            List<Object> objs = new ArrayList<>();
            map.forEach((key, value) -> {
                objs.add(value == null ? "" : value);
            });
            retList.add(objs);
        }
        return retList;
    }

    @Override
    public PageInfo dataSqlPageInfo(String tableId, String sql, int pageNum, int pageSize) {
        List<Map<String, Object>> lists = dataSqlQueryListCamelCase(tableId, sql, pageNum, pageSize);
        //DataSourceManager.reset2defaultDataSource();
        List<Object> listData = new ArrayList<>();
        // 动态生成内存类
        for (Map<String, Object> map : lists) {
            // 动态生成一个bean
            Object obj = BeanCreater.generateAndSetObject(map);
            listData.add(obj);
        }
        PageInfo pageInfo = new PageInfo(listData);
        return pageInfo;
    }

    @Override
    public PageInfo dataQueryPageInfoBySqlColumn(String tableId, String[] sqlColumn, String whereCond, int pageNum, int pageSize) throws Exception {
        //查表重组 sql
        DataFromVo tableFrom = dataSourceDao.getConnIdByTableId(tableId);
        if (tableFrom == null) {
            log.error("{} 表不存在", tableId);
            throw new Exception(tableId + " 表不存在");
        }
        //new DataSourceManager().setCurrentDataSource(tableFrom.getConnId());
        // PageHelper.startPage(pageNum, pageSize);
        StringBuilder sb = new StringBuilder();

        //将 sqlColumn 中的所有 sql 列名转成大写带下划线形式，如 pojo 为 simpleName -> SIMPLE_NAME
        String[] sqldeCamel = new String[1];
        if (sqlColumn != null && sqlColumn.length > 0) {
            sqldeCamel = new String[sqlColumn.length];
            for (int i = 0; i < sqldeCamel.length; i++) {
                sqldeCamel[i] = DataUnitUtils.isUpperCase(sqlColumn[i]) ? DataUnitUtils.deCamelCase(sqlColumn[i]) : sqlColumn[i];
            }

            sb.append("select ");
            if (sqldeCamel == null || sqldeCamel.length == 0) {
                sb.append(" * ");
            } else {
                for (int i = 0; i < sqlColumn.length - 1; i++) {
                    sb.append(sqldeCamel[i])
                            .append(" ,");
                }
                sb.append(sqldeCamel[sqldeCamel.length - 1]).append(" ");
            }

            sb.append(" from ");
            sb.append(tableFrom.getSrcTable());
            if (whereCond != null && !"".equals(whereCond)) {
                sb.append(" where ").append(whereCond);
            }

            // todo  增加 group by

            // todo  增加 having

            // todo  增加 order by

            // todo  增加 limit --校验是否与 PageHelper 插件冲突

            // todo 构造各级子查询
        }
        String sql = sb.toString();

        //当sql合法时，才允许执行
        List<List> lists = new ArrayList<>();
        if (sql != null && !"".equals(sql)) {
//            lists = dataSqlQueryListObjects(tableId, sql, pageNum, pageSize);
            List<Map<String, Object>> list = dataSqlQueryListCamelCase(tableId, sql, pageNum, pageSize);
            for (Map<String, Object> map : list) {
                List<Object> objs = new ArrayList<>();

                map.forEach((key, value) -> {
                    objs.add(value == null ? "" : value);
                });
                lists.add(objs);
            }
        }
        PageInfo pageInfo = new PageInfo(lists);
        //DataSourceManager.reset2defaultDataSource();
        return pageInfo;
    }

    private String getDbTypeByTableId(String connId) {
        DbLinkPo dbLinkPo = dbLinkMapper.selectDbLinkByPrimaryKey(connId);
        //  new DataSourceManager().setCurrentDataSource(DataFromVo.getConnId());
        // 强制读取 ds.jdbcUrl 私有属性，用以根据 jdbcUrl 字段来判断数据源类型
        String dataSourceType = "";
        String dbtype = dbLinkPo.getDbtype();
        if ("0".equals(dbtype)) {
            dataSourceType = "oracle";
        } else if ("2".equals(dbtype)) {
            dataSourceType = "mysql";
        } else if ("3".equals(dbtype)) {
            dataSourceType = "goldilocks";
        }
        return dataSourceType;
    }

    @Override
    public List<Map<String, Object>> choiceTable(String tableId) {

        List<Map<String, Object>> list = new ArrayList<>();
        //1.根据 tableId 查询连接池 id
        DataFromVo dataFromVo = dataSourceDao.getConnIdByTableId(tableId);
        String connId = dataFromVo.getConnId();
        //2.根据 connId 查询数据源类型
        String dataSourceType = getDbTypeByTableId(connId);
        // fixme 目前仅处理项目上当前使用到的 oracle 类型,其余类型用到了自行在此处理
        //3.根据数据源类型生成查询表注释语句
        if (!"".equals(dataSourceType) && "oracle".equals(dataSourceType)) {
            //4.查询
            String srcTable = dataFromVo.getSrcTable() == null ? "" : dataFromVo.getSrcTable().toUpperCase(Locale.ROOT);
            String sql = "select * from user_col_comments WHERE table_name ='" + srcTable + "'";
            list = dbLinkUtils.getSqlResult(connId, sql);
        } else if (!"".equals(dataSourceType) && "mysql".equals(dataSourceType)) {

        } else if (!"".equals(dataSourceType) && "goldilocks".equals(dataSourceType)) {

        }
        // DataSourceManager.reset2defaultDataSource();
        return list;
    }

    @Override
    public List<TableStructure> getColumnTypes(String tableId, String type, String unitId) {
        List<String> filedList = getAllDataFiled(unitId, "1");
        Map<String, String> filedMap = new HashMap<>();
        for(String str: filedList) {
            filedMap.put(str.toUpperCase(), str.toUpperCase());
        }

        List<TableStructure> retList = new ArrayList<>();

        //1.根据 tableId 查询连接池 id
        DataFromVo dataFromVo = dataSourceDao.getConnIdByTableId(tableId);
        String connId = dataFromVo.getConnId();
        //2.根据 connId 查询数据源类型
        String dataSourceType = getDbTypeByTableId(connId);

        // fixme 目前仅处理项目上当前使用到的 oracle 类型,其余类型用到了自行在此处理
        //3.根据数据源类型生成查询表注释语句
        if (!"".equals(dataSourceType) && "oracle".equals(dataSourceType)) {
            //4.根据连接池查询和来源表查询所有表的列及列的类型
            // SELECT data_type FROM User_Tab_Cols s WHERE s.column_name='xx' AND s.table_name='tt';
            String srcTable = dataFromVo.getSrcTable() == null ? "" : dataFromVo.getSrcTable().toUpperCase(Locale.ROOT);
            String sql = "SELECT s.COLUMN_NAME, s.data_type,b.COMMENTS FROM User_Tab_Cols s,user_col_comments b WHERE s.COLUMN_NAME = b.COLUMN_NAME AND s.TABLE_NAME =b.TABLE_NAME AND s.table_name='" + srcTable + "'";
            List<Map<String, Object>> list = dbLinkUtils.getSqlResult(connId, sql);
            for (Map<String, Object> map : list) {
                TableStructure ts = new TableStructure();
                ts.setColumnName((String) map.get("COLUMN_NAME"));
                String colName = (String) map.get("COLUMN_NAME");
                String value = filedMap.get(colName.toUpperCase());
                // 判断是不是维度度量字段
                if( value == null) {
                    continue;
                }
                String data_type = (String) map.get("DATA_TYPE");
                switch (type) {
                    //1-日期
                    case "1":
                        if (data_type.toLowerCase(Locale.ROOT).contains("date")
                                || data_type.toLowerCase(Locale.ROOT).contains("timestamp")
                        ) {
                            ts.setColumnType(data_type);
                            ts.setComment((String) map.get("COMMENTS"));
                            retList.add(ts);
                        }
                        break;
                    //2-文本
                    case "2":
                        if (data_type.toLowerCase(Locale.ROOT).contains("char")) {
                            ts.setColumnType(data_type);
                            ts.setComment((String) map.get("COMMENTS"));
                            retList.add(ts);
                        }
                        break;
                    //3-数值
                    case "3":
                        if (data_type.toLowerCase(Locale.ROOT).contains("number")
                                || data_type.toLowerCase(Locale.ROOT).contains("long")
                                || data_type.toLowerCase(Locale.ROOT).contains("integer")
                                || data_type.toLowerCase(Locale.ROOT).contains("binary_")
                                || data_type.toLowerCase(Locale.ROOT).contains("float")
                        ) {
                            ts.setColumnType(data_type);
                            ts.setComment((String) map.get("COMMENTS"));
                            retList.add(ts);
                        }
                        break;
                    default:
                        continue;
                }
            }
        } else if (!"".equals(dataSourceType) && "mysql".equals(dataSourceType)) {

        } else if (!"".equals(dataSourceType) && "goldilocks".equals(dataSourceType)) {

        }
        // DataSourceManager.reset2defaultDataSource();
        return retList;
    }

    @Override
    public PageInfo getColumnGroupBy(String tableId, String[] sqlColumn, Integer pageNum, Integer pageSize) {

        //1.根据 tableId 查询连接池 id
        DataFromVo dataFromVo = dataSourceDao.getConnIdByTableId(tableId);
        String connId = dataFromVo.getConnId();
        //new DataSourceManager().setCurrentDataSource(DataFromVo.getConnId());
        StringBuilder cols = new StringBuilder();
        StringBuilder endCols = new StringBuilder();
        //用于返回的空信息
        //添加空信息，本应用放在后面，但为了省事，放在这里一起处理
        LinkedHashMap<String, Object> retMap = new LinkedHashMap<>();
        retMap.put("rowId", new ArrayList<Object>());
        for (int i = 0; i < sqlColumn.length; i++) {
            String singleColumn = sqlColumn[i];
            if (!DataUnitUtils.isAcronym(singleColumn) && !singleColumn.contains("_")) {
                singleColumn = !DataUnitUtils.isUpperCase(singleColumn) ? DataUnitUtils.deCamelCase(singleColumn) : singleColumn;
            }
            //定义 select 和 from 之间的列
            cols.append("\"")
                    .append(singleColumn.toUpperCase(Locale.ROOT))
                    .append("\"");
//                    .append(" ")
//                    .append(sqlColumn[i]);
            if (i != sqlColumn.length - 1) {
                cols.append(",");
            }

            //定义 group by 后的列
            endCols.append("\"")
                    .append(singleColumn.toUpperCase(Locale.ROOT))
                    .append("\"");
            if (i != sqlColumn.length - 1) {
                endCols.append(",");
            }
            retMap.put(sqlColumn[i], new ArrayList<Object>());
        }
        String sql = "select " + cols.toString() + " from " + dataFromVo.getSrcTable() + " group by " + endCols.toString();
        List<Map<String, Object>> list = dbLinkUtils.getPageSqlResult(tableId,connId, sql, pageNum, pageSize).getRecords();
        PageInfo pageInfo = new PageInfo(list);
        list.forEach(map -> {
            map.forEach((key, value) -> {
                ArrayList listx = (ArrayList) (retMap.get(DataUnitUtils.camelSqlFieldName(key)) == null ? retMap.get(key) : retMap.get(DataUnitUtils.camelSqlFieldName(key)));
                listx.add(value == null ? "" : value.toString());
            });
        });
        //DataSourceManager.reset2defaultDataSource();
        ArrayList arrayList = new ArrayList();
        arrayList.add(retMap);
        pageInfo.setList(arrayList);
        return pageInfo;
    }

    @Override
    public void insertUnitCvg(String unitId, List<NumberSelected> numberFields) {
        for (int i = 0; i < numberFields.size(); i++) {
            final int seqNo = i;
            //入库对象准备
            NumberSelected ns = numberFields.get(i);
            String colEn = ns.getColEn();
            //如果 前端传过来的列名为 null，表示度量域中一个值也没有，即不插入任何数据到数据库中
            if (colEn == null) {
                continue;
            }
            UnitCvg unitCvg = new UnitCvg();
            unitCvg.setUnitId(unitId);
            String uuid = "" + uidGenerator.getUID();
            unitCvg.setFldId(uuid);
            unitCvg.setFldEnname(colEn);
            unitCvg.setFldCnname(ns.getShowName());
            unitCvg.setType("1");
            unitCvg.setSrcType("1");
            unitCvg.setTableId("noValue");
            unitCvg.setFunName(ns.getFastCalc());
            unitCvg.setValue("11");
            unitCvg.setDefaultVal("111");
            // seqNo 默认起始值为 0 ，这里+1表示计数从1 开始，0留待以后可能的扩展
            unitCvg.setSeqno(new BigDecimal(seqNo + 1));
            unitCvg.setFlag(Boolean.valueOf(ns.isBeShow()).toString());
            unitCvg.setCreatedBy("admin");
            unitCvg.setCreatedDt("now");
            unitCvg.setUpdatedBy("admin");
            unitCvg.setUpdatedDt("now");
            //DataSourceManager.reset2defaultDataSource();
            dataUnitsDao.insertOneUnitCvg(unitCvg);
        }
    }

    @Override
    public void deleteAllUnitKeyByUnitId(String unitId, String type) {
        dataUnitsDao.deleteUnitKeyByUnitId(unitId, type);
    }

    @Override
    public int insertOneUnitGroup(UnitGroup unitGroup) {
        return dataUnitsDao.insertOneUnitGroup(unitGroup);
    }

    //入库组件维度信息
    @Override
    public void insertUnitKey(String unitId, List<TextSelected> textFields, String type) {

        //通用属性填充
        String createBy = "admin";
        String updateBy = "admin";
        String nowDate = "now";//DateUtils.formatByDateTimePattern(new Date());

        for (int i = 0; i < textFields.size(); i++) {
            final int seqNo = i;
            TextSelected ts = textFields.get(i);
            String colEn = ts.getColEn();
            //如果 前端传过来的列名为 null，表示维度域中一个值也没有，即不插入任何数据到数据库中
            if (colEn == null) {
                continue;
            }
            //todo 数据库无该字段，待业务确认
            String sortColName = ts.getSortColName();
            String showName = ts.getShowName();
            //todo 数据库无该字段，待业务确认
            //String groupId = prop.getGroupId();
            //todo 数据库无该字段，待业务确认
            String orderType = ts.getOrderType();
            //用于入库的对象
            UnitKey unitKey = new UnitKey();
            unitKey.setUnitId(unitId);
            //dao中赋uuid
            String uuid = "" + uidGenerator.getUID();
            unitKey.setFldId(uuid);
            unitKey.setFldEnname(colEn);
            unitKey.setFldCnname(showName);
            //仅有维度和度量时,这里为1,当将维度分为行维度和列维度时,行维度为 1,列维度为2
            unitKey.setType(type);
            unitKey.setSrcType(type);
            unitKey.setTableId("1");
            unitKey.setValue("开发测试");
            unitKey.setDefaultVal("开发测试");
            // seqNo 默认起始值为 0 ，这里+1表示计数从1 开始，0留待以后可能的扩展
            unitKey.setSeqno(new BigDecimal(seqNo + 1));
            //Flag字段利用为 前端是否显示,即 beShow
            unitKey.setFlag(Boolean.valueOf(ts.isBeShow()).toString());
            //暂时将分组类型存到 CreateBy 域中
            unitKey.setCreatedBy(ts.getGroupType() == null ? " " : ts.getGroupType());
            unitKey.setCreatedDt(nowDate);
            unitKey.setUpdatedBy(updateBy);
            unitKey.setUpdatedDt(nowDate);
            //插入数据到组件"维度信息表"
            dataUnitsDao.insertOneUnitKey(unitKey);

            ///////////  处理图形相关  t_pic_attr 主表 关联其它图形子表
            //图形颜色
            String columnColor = ts.getColumnColor();
            //附加的一些图形 todo,需要处理多条件图形入库
            List<SharpCondition> columnSharp1 = ts.getColumnSharp();
            PictureAttr pa = new PictureAttr();
            pa.setUnitId(uuid);
            pa.setFldId(colEn);
        }
    }

    @Override
    public void insertUnitCondition(Map<String, String> cond) {
            try {
                //DataSourceManager.reset2defaultDataSource();
                tDataCondMapper.insertOneTDataCond(cond);
            } catch (Exception e) {
                //fixme 如果发生插入错误，表示数据库中有历史数据或前依赖数据，这种情况需要忽略
                e.printStackTrace();
            }
            // fixme 需要同步入到关联库
    }


    @Override
    public void insertOneUnitBase(UnitBasePo unitBasePo) throws Exception {
        // 向组件基本信息表中插入一条记录,uuid仅在 service 层生成，controller 层只处理与前端相关业务
        long uid = uidGenerator.getUID();
        unitBasePo.setUnitId("" + uid);
        dataUnitsDao.insertOneUnitBase(unitBasePo);
    }


    /**
     * @param tableId      表 id 或 后期调整为数据集 id
     * @param textFields1  维度集合
     * @param numberFields 度量集合
     * @param pageNum      页号
     * @param pageSize     每页尺寸
     * @return 返回 PageHelper 分页
     * @throws Exception
     * @description 分组表查询服务
     */
    @Override
    public PageInfo groupByTableQuery(String tableId, List<TextSelected> textFields1, List<NumberSelected> numberFields, List<TDataCondPo> condList,
                                      Map<String, String> defGroupSqlWhere, String filterWhereCond, Map<String, String> defSortSqlOrderBy, int pageNum, int pageSize) throws Exception {

        synchronized (lock) {
            //查表重组 sql
            DataFromVo tableFromVo = dataSourceDao.getConnIdByTableId(tableId);
            if (tableFromVo == null && tableFromVo.getSrcTable() == null && "".equals(tableFromVo.getSrcTable())) {
                log.error("{} 表不存在", tableId);
                throw new Exception(tableId + " 表不存在");
            }
            //new DataSourceManager().setCurrentDataSource(tableFromVo.getConnId());
            //PageHelper.startPage(pageNum, pageSize);

            // 处理维度，组成 select from 间的分组条件，例如：select id,name from ...
            String[] sqlDeCamelText = new String[1];
            if (textFields1 != null && textFields1.size() > 0) {
                //将 sqlColumn 中的所有 sql 列名转成大写带下划线形式，如 pojo 为 simpleName -> SIMPLE_NAME
                sqlDeCamelText = new String[textFields1.size()];
                for (int i = 0; i < sqlDeCamelText.length; i++) {
                    TextSelected ts = textFields1.get(i);
                    String colEn = ts.getColEn();
                    sqlDeCamelText[i] = !DataUnitUtils.isUpperCase(colEn) ? DataUnitUtils.deCamelCase(colEn) : colEn;
                }

            }
            // 处理度量，组成 select from 间带函数的分组条件,例如:select id,sum(name) from ...
            String[] sqlDeCamelNumber = new String[1];
            if (textFields1 != null && numberFields.size() > 0) {
                //将 sqlColumn 中的所有 sql 列名转成大写带下划线形式，如 pojo 为 simpleName -> SIMPLE_NAME
                sqlDeCamelNumber = new String[numberFields.size()];
                for (int i = 0; i < sqlDeCamelNumber.length; i++) {
                    NumberSelected ts = numberFields.get(i);
                    // 根据度量的属性组函数,
                /*
                 fixme 此处理论上要根据数据源类型使用函数.因为不同类型的数据源的类型相同功能的函数可能不一样,留待后期开发
                 比如 mysql 和 oracle 的函数就不完全对应,需要有针对性的处理
                 */
                    String colEn = ts.getColEn();
                    String deCamelCase = !DataUnitUtils.isUpperCase(colEn) ? DataUnitUtils.deCamelCase(colEn) : colEn;
                    // 前端快速计算 FastCalc 和 汇总计算 TotalCalc() 只允许一个存在
                    //首先判断 汇总计算, 如果汇总计算不存在配置,则使用 快速计算
                    String funcName = ts.getTotalCalc() == null ? (ts.getFastCalc() == null ? "" : ts.getFastCalc()) : ts.getTotalCalc();
                    if (funcName != null && !"".equals(funcName)) {
                        // 多种函数按函数要求的格式进行组合
                        String funcNameOri = funcName + "(" + deCamelCase + ")";
                        sqlDeCamelNumber[i] = funcNameOri.toUpperCase(Locale.ROOT);
                    }
                }
            }

            //默认分组时的 sql 语句
            String sql = "";
//        sql = unDefGroupSql(textFields1, tableFromVo, sqlDeCamelText, sqlDeCamelNumber);

            //最后返回的 list
            List<Map<String, Object>> lists = new ArrayList<>();
            //当前 lists map 中的层级
            Map mapLevel = null;
            // 判断 TextSelect List 是否有值
            if (textFields1.size() > 0) {
                for (TextSelected ts : textFields1) {
                    boolean beShow = ts.isBeShow();
                    if (!beShow) {
                        continue;
                    } else {
                        if ("same".equals(ts.getGroupType())) {
                            String colEn = ts.getColEn() == null ? "" : ts.getColEn();
                            //参考:select MODEL_ID,count(MODEL_ID) from t_data_cond tdc where MODEL_ID in ('123','124') group by MODEL_ID  ;
                            sql = unMakeExecSql(colEn, tableFromVo.getSrcTable(), sqlDeCamelNumber, "", filterWhereCond);
                        } else if ("def".equals(ts.getGroupType())) {
                    /*
                    select MODEL_ID,count(MODEL_ID) from t_data_cond tdc where MODEL_ID in ('123','124') group by MODEL_ID union
                    select '分组名',count(MODEL_ID) from t_data_cond tdc where MODEL_ID ='123' group by MODEL_ID  union
                    select  '34',count(MODEL_ID) from t_data_cond tdc where MODEL_ID ='124' group by MODEL_ID;
                     */
                            //todo 待校验
                            sql = makeExcSql(textFields1, tableFromVo, sqlDeCamelText, sqlDeCamelNumber, "", filterWhereCond);
                        }
                    }
                    //当sql合法时，才允许执行
                    List<Map<String, Object>> listTmp = null;
                    if (sql != null && !"".equals(sql)) {
                        listTmp = sqlQuery2(tableId, numberFields, pageNum, pageSize, sqlDeCamelText, sqlDeCamelNumber, sql);
                    }
                    //将结果合于上一个集合中
                    if (lists != null && lists.size() > 0) {
                        for (Map<String, Object> map : lists) {
                            //map.get()
                        }
                    } else {
                        lists = listTmp;
                    }
                }
            } else {
                //如果 维度 和 度量 均为空,则不执行任何查询
                sql = makeExcSql(textFields1, tableFromVo, sqlDeCamelText, sqlDeCamelNumber, "", filterWhereCond);
                if (sql != null && !"".equals(sql)) {
                    lists = sqlQuery2(tableId, numberFields, pageNum, pageSize, sqlDeCamelText, sqlDeCamelNumber, sql);
                }
            }

            PageInfo pageInfo = new PageInfo(lists);
            //DataSourceManager.reset2defaultDataSource();
            return pageInfo;
        }
    }

    @Override
    public List<Map<String, Object>> sqlQuery2(String tableId, List<NumberSelected> numberFields, int pageNum,
                                               int pageSize, String[] sqlDeCamelText, String[] sqlDeCamelNumber, String sql) {
        List<Map<String, Object>> lists = new ArrayList<>();
        if (sql != null && !"".equals(sql)) {
            List<Map<String, Object>> mapList = dataSqlQueryList(tableId, sql, pageNum, pageSize);
            for (Map<String, Object> map : mapList) {
                LinkedHashMap<String, Object> tempMap = new LinkedHashMap<>();
                //维度放在前面
                for (String text : sqlDeCamelText) {
                    Object o = map.get(text);
                    tempMap.put(DataUnitUtils.camelSqlFieldName(text), o);
                }
                //度量放在维度的后面
                for (int i = 0; i < sqlDeCamelNumber.length; i++) {
                    String number = sqlDeCamelNumber[i];
                    Object o = map.get(number);
                    //todo 这里需要适配到 ACCOUNT_NAME 和 accountName 两种情况
                    String colEn = numberFields.get(i).getColEn();
                    if (colEn.contains("_")) {
                        colEn = DataUnitUtils.camelSqlFieldName(numberFields.get(i).getColEn());
                    }
                    tempMap.put(colEn, o);
                }
                lists.add(tempMap);
            }
        }
        //DataSourceManager.reset2defaultDataSource();
        return lists;
    }

    @Override
    public List<UnitBaseVo> queryUnitBaseById(String dashbordId) {
        List<UnitBaseVo> list = dataUnitsDao.queryUnitBaseById(dashbordId);
        return list;
    }

    @Override
    public List<UnitKey> getAllUnitKey(String unitId, String type) {
        return dataUnitsDao.getAllUnitKey(unitId, type);
    }

    public List<String> getAllDataFiled(String unitId, String type) {
        List<UnitKey> wd = dataUnitsDao.getAllUnitKey(unitId, type);
        List<UnitCvg> dl = dataUnitsDao.getAllUnitCvg(unitId);
        List<String> fileds = new ArrayList<>();
        for(UnitKey u: wd) {
            fileds.add(humpToLine(u.getFldEnname()));
        }
        for(UnitCvg u: dl) {
            fileds.add(humpToLine(u.getFldEnname()));
        }
        return fileds;
    }

    @Override
    public void deleteAllUnitCvgByUnitId(String unitId) {
        dataUnitsDao.deleteAllUnitCvgByUnitId(unitId);
    }

    @Override
    public void deleteAllUnitCondition(String unitId) {
        dataUnitsDao.deleteAllCondition(unitId);
    }

    @Override
    public Integer getUnitType(String unitId) {
        return dataUnitsDao.getUnitType(unitId);
    }

    @Override
    public List<UnitCvg> getAllUnitCvg(String unitId) {
        return dataUnitsDao.getAllUnitCvg(unitId);
    }

    @Override
    public Map<String, String> getAllCondition(String unitId) {
        Map<String, String> allCondition = dataUnitsDao.getAllCondition(unitId);

        return allCondition;
    }

    @Override
    public void deleteAllWidgets(String unitId, String type) {
        dataUnitsDao.deleteAllWidgets(unitId, type);
    }

    @Override
    public void insertAdnInfo(String unitId, String widgets, String type) {
        dataUnitsDao.insertAdnInfo(unitId, widgets, type);
    }

    @Override
    public String getAllWidgets(String unitId, String type) {
        String str = dataUnitsDao.getAllWidgets(unitId, type);
        return str;
    }

    private String unMakeExecSql(String colEn, String srcTable, String[] sqlDeCamelNumber, String whereCond, String filterUnitWhereCond) {

        String curColumn = !DataUnitUtils.isUpperCase(colEn) ? DataUnitUtils.deCamelCase(colEn) : colEn;
        ///////////       组最终 sql      ///////////
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ");
        sb.append(" ").append(curColumn).append(" ,");

        // 将度量信息组入列中
        for (int i = 0; i < sqlDeCamelNumber.length; i++) {
            sb.append(sqlDeCamelNumber[i]);
            if (i != sqlDeCamelNumber.length - 1) {
                sb.append(" ,");
            }
        }

        sb.append(" FROM ");
        sb.append(srcTable);

        // 增加 where 条件
        sb.append(" where 1=1 ");
        if (whereCond != null && !"".equals(whereCond)) {
            sb.append(whereCond);
        }

        // 组过滤组件的 where 条件
        if (filterUnitWhereCond != null && !"".equals(filterUnitWhereCond)) {
            sb.append(filterUnitWhereCond);
        }

        ///////////// 增加 group by
        // 遍历所有字段执行组分组信息操作
        sb.append(" GROUP BY ");
        sb.append(" ")
                .append(curColumn).
                append(" ");

        // 增加 having

        // 增加 order by

        // 增加 limit --校验是否与 PageHelper 插件冲突

        // 造各级子查询

        return sb.toString();
    }

    private String makeExcSql(List<TextSelected> textFields1, DataFromVo tableFromVo, String[]
            sqlDeCamelText, String[] sqlDeCamelNumber, String whereCond, String filterUnitWhereCond) {
        ///////////       组最终 sql      ///////////
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ");
        if (sqlDeCamelText == null || sqlDeCamelText.length == 0 || sqlDeCamelText[0] == null) {
            sb.append(" * ");
        } else {

            //... 组 select 与 from 之间的列

            //  如果分组为 same，即未分组（或默认分组），则保持原样
            // 将维度信息组入列中
            for (int i = 0; i < sqlDeCamelText.length; i++) {
                sb.append(sqlDeCamelText[i]);
                if (i == sqlDeCamelText.length - 1) {
                    sb.append(" ,");
                }
            }

            // 将度量信息组入列中
            for (int i = 0; i < sqlDeCamelNumber.length; i++) {
                sb.append(sqlDeCamelNumber[i]);
                if (i != sqlDeCamelNumber.length - 1) {
                    sb.append(" ,");
                }
            }
        }

        sb.append(" FROM ");
        sb.append(tableFromVo.getSrcTable());

        // 增加 where 条件
        if (whereCond != null && !"".equals(whereCond)) {
            sb.append(" where ").append(whereCond);
        }

        ///////////// 增加 group by
        // 遍历所有字段执行组分组信息操作
        sb.append(" GROUP BY ");
        if (sqlDeCamelText != null && (sqlDeCamelText.length != 0 && sqlDeCamelText[0] != null)) {
            for (int i = 0; i < sqlDeCamelText.length; i++) {
                //1.获取该字段分组类型,same 表示与select from 间的字段相同，def 表示自定义分组
                TextSelected ts = textFields1.get(i);
                //same 表示与select from 间的字段相同，def 表示自定义分组
                String groupType = ts.getGroupType();

                if (groupType != null && "same".equals(groupType)) {
                    sb.append(sqlDeCamelText[i]);
                } else if (groupType != null && "def".equals(groupType)) {
                    //todo 未处理 def 自定义分组类型
                }
                //如果不是最后一个列，则每个列后增加一个逗号，例如: group by id,name,age from
                if (i != sqlDeCamelText.length - 1) {
                    sb.append(" ,");
                }
            }
        } else {
            //无任何维度与指标,不返回任何数据
            sb.setLength(0);
        }

        // 增加 having

        // 增加 order by

        // 增加 limit --校验是否与 PageHelper 插件冲突

        // 造各级子查询

        return sb.toString();
    }

    /**
     * 第一个阶段，先真接查 oracle 数据库中的表头，第二个阶段从即席分析系统查询列配置表中的表头列信息
     *
     * @description 根据表的 tableId 获取表头信息
     */
    @Override
    public Map<String, String> getColumnHeadInfo(String tableId) throws Exception {
        String sql = null;
        LinkedHashMap<String, String> map2 = new LinkedHashMap<>();
        //查表重组 sql
        DataFromVo tableFromVo = dataSourceDao.getConnIdByTableId(tableId);
        if (tableFromVo == null || tableFromVo.getSrcTable() == null || "".equals(tableFromVo.getSrcTable())) {
            log.error("{} 表不存在", tableId);
            throw new Exception(tableId + " 表不存在");
        }
        TDataFromPo tDataFromPo = tDataFromService.selectOneTDataFrom(tableId);
        DbLinkPo dbLinkPo = dbLinkMapper.selectDbLinkByPrimaryKey(tDataFromPo.getConnId());
        if ("0".equals(dbLinkPo.getDbtype())) {
            //new DataSourceManager().setCurrentDataSource(tableFromVo.getConnId());
            sql = "SELECT a.column_name,b.comments FROM user_tab_columns a, user_col_comments b WHERE" +
                    " a.table_name = b.table_name AND a.column_name = b.column_name AND a.table_name=UPPER('" + tableFromVo.getSrcTable() + "')";
        } else if ("2".equals(dbLinkPo.getDbtype())) {
            sql = "select COLUMN_NAME,COLUMN_COMMENT from information_schema.COLUMNS where table_name = '" + tableFromVo.getSrcTable() + "'";
        }
        List<Map<String, Object>> list = dbLinkUtils.getSqlResult(tableFromVo.getConnId(), sql);
        list.forEach(map -> {
            String columnName = (String) map.get("COLUMN_NAME");
            String comments = (String) map.get("COMMENTS");
            map2.put(columnName, comments);
        });
        //DataSourceManager.reset2defaultDataSource();
        return map2;
    }

    @Override
    public List<ColumnInfo> getColumnInfo(String tableId) throws Exception {
        List<ColumnInfo> columnInfoList = new ArrayList<>();
        //查表重组 sql
        DataFromVo tableFromVo = dataSourceDao.getConnIdByTableId(tableId);
        if (tableFromVo == null || tableFromVo.getSrcTable() == null || "".equals(tableFromVo.getSrcTable())) {
            log.error("{} 表不存在", tableId);
            throw new Exception(tableId + " 表不存在");
        }
        //new DataSourceManager().setCurrentDataSource(tableFromVo.getConnId());
        String sql = "SELECT a.column_name,a.data_type,b.comments FROM user_tab_columns a, user_col_comments b WHERE" +
                " a.table_name = b.table_name AND a.column_name = b.column_name AND a.table_name=UPPER('" + tableFromVo.getSrcTable() + "')";
        List<Map<String, Object>> list = dbLinkUtils.getSqlResult(tableFromVo.getConnId(), sql);
        list.forEach(map -> {
            ColumnInfo ci = new ColumnInfo();
            String columnName = (String) map.get("COLUMN_NAME");
            String comments = (String) map.get("COMMENTS");
            String dataType = (String) map.get("DATA_TYPE");
            ci.setColumnCommit(comments);
            ci.setColumnType(dataType);
            ci.setColumnName(columnName);
            columnInfoList.add(ci);

        });
        // DataSourceManager.reset2defaultDataSource();
        return columnInfoList;
    }

    @Override
    public List<Map<String, Object>> defaultSqlQueryList(String s) {
        List<Map<String, Object>> list = dataUnitsDao.generalDataQuery(s);
        return list;
    }

    @Override
    public String getTableIdByModelId(String modelId) {
        return dataUnitsDao.getTableIdByModelId(modelId);
    }

    @Override
    public void updateUniteBase(UnitBasePo unitBasePo) {
        dataUnitsDao.updateUnitBase(unitBasePo);
    }

    @Override
    public String selectModelByUnitId(String unitId) {
        return dataUnitsDao.selectModelByUnitId(unitId);
    }

    @Override
    public UnitBasePo selectUnitInfoById(String unitId) {
        return dataUnitsDao.selectUnitInfoById(unitId);
    }

    @Override
    public List<AddColVo> getAllFunc(String type) {
        List<AddColVo> list = new ArrayList<>();
        List<TDataNewfun> allFunc = dataUnitsDao.getAllFunc(type);

        //因前端要求与列信息返回一样,故在这里使用 AddColVo 与 ColVo 复用为函数返回 VO
        AddColVo addColVo1 = new AddColVo();
        addColVo1.setId("1");
        addColVo1.setName("聚合函数");
        addColVo1.setDescription("");
        addColVo1.setChildren(new ArrayList<ColVo>());

        AddColVo addColVo2 = new AddColVo();
        addColVo2.setId("2");
        addColVo2.setName("快速计算函数");
        addColVo2.setDescription("");
        addColVo2.setChildren(new ArrayList<ColVo>());

        AddColVo addColVo3 = new AddColVo();
        addColVo3.setId("3");
        addColVo3.setName("数学和三角函数");
        addColVo3.setDescription("");
        addColVo3.setChildren(new ArrayList<ColVo>());

        AddColVo addColVo4 = new AddColVo();
        addColVo4.setId("4");
        addColVo4.setName("日期函数");
        addColVo4.setDescription("");
        addColVo4.setChildren(new ArrayList<ColVo>());

        AddColVo addColVo5 = new AddColVo();
        addColVo5.setId("5");
        addColVo5.setName("文本函数");
        addColVo5.setDescription("");
        addColVo5.setChildren(new ArrayList<ColVo>());

        AddColVo addColVo6 = new AddColVo();
        addColVo6.setId("6");
        addColVo6.setName("逻辑函数");
        addColVo6.setDescription("");
        addColVo6.setChildren(new ArrayList<ColVo>());

        AddColVo addColVo7 = new AddColVo();
        addColVo7.setId("7");
        addColVo7.setName("其他函数");
        addColVo7.setDescription("");
        addColVo7.setChildren(new ArrayList<ColVo>());

        int i1 = 0;
        int i2 = 0;
        int i3 = 0;
        int i4 = 0;
        int i5 = 0;
        int i6 = 0;
        int i7 = 0;

        for (TDataNewfun tf : allFunc) {
            String funClass = tf.getFunClass();
            ColVo colVo = new ColVo();
            colVo.setEnName(tf.getFunName() == null ? "" : tf.getFunName());
            colVo.setDescription((tf.getFunMark() == null || "".equals(tf.getFunMark())) ? "" : tf.getFunMark());
            colVo.setName(tf.getFunName() == null ? "" : tf.getFunName());
            switch (funClass) {
                //1.聚合函数
                case "1":
                    addColVo1.setCount(++i1);
                    List<ColVo> children1 = addColVo1.getChildren();
                    children1.add(colVo);
                    break;
                //2.快速计算函数
                case "2":

                    addColVo2.setCount(++i2);
                    List<ColVo> children2 = addColVo2.getChildren();
                    children2.add(colVo);
                    break;
                //3.数学和三角函数
                case "3":
                    addColVo3.setCount(++i3);
                    List<ColVo> children3 = addColVo3.getChildren();
                    children3.add(colVo);
                    break;
                //4.日期函数
                case "4":
                    addColVo4.setCount(++i4);
                    List<ColVo> children4 = addColVo4.getChildren();
                    children4.add(colVo);
                    break;
                //5.文本函数
                case "5":
                    addColVo5.setCount(++i5);
                    List<ColVo> children5 = addColVo5.getChildren();
                    children5.add(colVo);
                    break;
                //6. 逻辑函数
                case "6":
                    addColVo6.setCount(++i6);
                    List<ColVo> children6 = addColVo6.getChildren();
                    children6.add(colVo);
                    break;
                //7.其他函数
                case "7":
                    addColVo7.setCount(++i7);
                    List<ColVo> children7 = addColVo7.getChildren();
                    children7.add(colVo);
                    break;
            }
        }
        list.add(addColVo1);
        list.add(addColVo2);
        list.add(addColVo3);
        list.add(addColVo4);
        list.add(addColVo5);
        list.add(addColVo6);
        list.add(addColVo7);
        return list;
    }

    @Override
    public String insertNewFld(DataNewFld dnf) {
        String colId = "";
        if (dnf.getFieldId() == null || "".equals(dnf.getFieldId())) {
            colId = DataUnitUtils.uuid();
            dnf.setFieldId(colId);
            dataUnitsDao.insertNewFld(dnf);
        } else {
            colId = dnf.getFieldId();
            dataUnitsDao.updateNewFld(dnf);
        }
        return colId;
    }

    @Override
    public String getFilterUnitCond(String unitId) {
        List<FilterUnitPo> filterUnitCond = dataUnitsDao.getFilterUnitCond(unitId);
        StringBuilder sbFor = new StringBuilder();
        for (int i = 0; i < filterUnitCond.size(); i++) {
            FilterUnitPo filterUnitPo = filterUnitCond.get(i);
            String value = filterUnitPo.getValue();
            if (value != null && !"".equals(value)) {
                FilterUnitValuePo fup = JSON.parseObject(value, FilterUnitValuePo.class);
                List<String> columnList = fup.getColumn();
                List<String> value1 = fup.getValue();
                String col1 = "";
                // columnList 合法性校验
                if (columnList != null && columnList.size() != 0) {
                    //只取第一列
                    col1 = columnList.get(0);
                    if (col1 == null || "".equals(col1)) {
                        continue;
                    }
                } else {
                    //如果列信息为空，直接进入下一次循环
                    continue;
                }
                // type 合法性校验
                // 0-单选 1-多选 2-区间
                String type = fup.getType();
                if (type == null || !("0".equals(type) | "1".equals(type) | "2".equals(type))) {
                    continue;
                }
                if ("0".equals(type)) {
                    if (value1 == null) {
                        continue;
                    }
                    String s = value1.get(0);
                    if (s == null) {
                        continue;
                    }
                    sbFor.append(" and ")
                            .append("\"")
                            .append(col1)
                            .append("\"")
                            .append(" = ")
                            .append("\'")
                            .append(s)
                            .append("\' ");
                } else if ("1".equals(type)) {
                    if (value1 == null || value1.size() == 0) {
                        continue;
                    }
                    sbFor.append("and ")
                            .append("\"")
                            .append(col1)
                            .append("\"")
                            .append(" in (");
                    for (int i1 = 0; i1 < value1.size(); i1++) {
                        sbFor.append("\'")
                                .append(value1.get(i1))
                                .append("\'");
                        if (i1 != value1.size() - 1) {
                            sbFor.append(",");
                        }
                    }
                    sbFor.append(") ");
                } else if ("2".equals(type)) {
                    Long maxValue = fup.getMaxValue();
                    Long minValue = fup.getMinValue();
                    if (maxValue == null || minValue == null) {
                        continue;
                    }
                    sbFor.append(" and ");

                    //交换最大最小值
                    if (minValue > maxValue) {
                        Long tmp = null;
                        tmp = minValue;
                        minValue = maxValue;
                        maxValue = tmp;
                    }
                    //构造区域  最后形式: and "columnName" = 'value' and "column2" in ("value1","value2") and ("column3"> 12 and "column3" < 24)
                    sbFor.append(" (").append("\"").append(col1).append("\"").append(" > ").append(minValue)
                            .append(" and ").append("\"").append(col1).append("\"").append(" < ").append(maxValue).append(") ");
                }
            }
        }
        return sbFor.toString();
    }

    @Override
    public List<ColumnInfo> getAllColumnByDatasetId(String datasetId) throws Exception {
        String tableId = dataUnitsDao.getTableIdByModelId(datasetId);
        List<ColumnInfo> columnInfo = getColumnInfo(tableId);
        return columnInfo;
    }

    @Override
    public Map<String, Object> getIntColumnInfo(String datasetId, String fieldName) {

        //列名反驼峰处理，即：accountName -> ACCOUNT_NAME
        if (!DataUnitUtils.isAcronym(fieldName)) {
            fieldName = DataUnitUtils.deCamelCase(fieldName);
        }

        String tableId = dataUnitsDao.getTableIdByModelId(datasetId);
        DataFromVo dataFromVo = dataSourceDao.getConnIdByTableId(tableId);
        //changeDataSourceByTableId(tableId);
        String maxSql = "SELECT MAX(" + fieldName + ") as max, MIN(" + fieldName + ") as min FROM " + dataFromVo.getSrcTable().toUpperCase(Locale.ROOT);
        //更换数据源
        //new DataSourceManager().setCurrentDataSource(dataFromVo.getConnId());
        //Map<String, Object> map = dbLinkUtils.getIntColumnInfo(maxSql);
        Map<String, Object> map = dbLinkUtils.getSqlResult(dataFromVo.getConnId(), maxSql).get(0);
        //DataSourceManager.reset2defaultDataSource();
        HashMap<String, Object> retMap = new HashMap<>();
        BigDecimal min = null;
        BigDecimal max = null;
        if (map != null) {
            min = (BigDecimal) map.get("MIN");
            max = (BigDecimal) map.get("MAX");
        } else {
            min = new BigDecimal("0");
            max = new BigDecimal("0");
        }
        if (max.compareTo(new BigDecimal("5")) < 0) {
            max = new BigDecimal("5");
        }
        //步长 step 值
        BigDecimal stepVal = new BigDecimal("0").equals((max.subtract(min)).divide(new BigDecimal(5))) ? new BigDecimal("1") : (max.subtract(min)).divide(new BigDecimal(5));
        retMap.put("min", min);
        retMap.put("max", max);
        retMap.put("step", stepVal);
        ArrayList<BigDecimal> ints = new ArrayList<>();
        BigDecimal temp = min;
        ints.add(new BigDecimal("0"));
        for (int i = 0; i < 5; i++) {
            if (i != 4) {
                ints.add(temp = temp.add(stepVal));
            } else {
                BigDecimal subtracted = max.subtract(min);
                BigDecimal[] res = subtracted.divideAndRemainder(new BigDecimal(5));
                //BigDecimal Quotient = res[0];//商
                BigDecimal remainderres = res[1];//余数
                ints.add(temp.add(stepVal).add(remainderres));
            }
        }
        retMap.put("interval", ints);
        return retMap;
    }

    @Override
    public Map<String, Object> getIntColumnInfo2(String datasetId, String fieldName, Integer pageNum, Integer pageSize) {

        //列名反驼峰处理，即：accountName -> ACCOUNT_NAME
        if (!DataUnitUtils.isAcronym(fieldName)) {
            fieldName = DataUnitUtils.deCamelCase(fieldName);
        }

        String tableId = dataUnitsDao.getTableIdByModelId(datasetId);
        DataFromVo dataFromVo = dataSourceDao.getConnIdByTableId(tableId);
        //changeDataSourceByTableId(tableId);

        String maxSql = "SELECT " + fieldName.toLowerCase(Locale.ROOT) + " FROM " + dataFromVo.getSrcTable().toUpperCase(Locale.ROOT);
        //更换数据源
        //new DataSourceManager().setCurrentDataSource(dataFromVo.getConnId());
        if (pageNum != null && pageSize != null) {
            PageHelper.startPage(pageNum, pageSize);
        }
        //List<Map<String, Object>> lists = dataUnitsDao.getIntColumnInfo2(maxSql);
        List<Map<String, Object>> lists = dbLinkUtils.getSqlResult(dataFromVo.getConnId(), maxSql);
        List<String> retList = new ArrayList<>();
        for (Map<String, Object> list : lists) {
            list.forEach((key, value) -> {
                if ((value instanceof String)) {
                    retList.add(value == null ? "" : (String) value);
                }
            });

        }
        PageInfo pageInfo = new PageInfo(lists);
        //DataSourceManager.reset2defaultDataSource();
        HashMap<String, Object> retMap = new HashMap<>();
        retMap.put("value", retList);
        pageInfo.setList(null);
        retMap.put("page", pageInfo);
        return retMap;
    }

    @Override
    public Map<String, Object> getIntColumnInfo3(String datasetId, String fieldName, String keyword) {
        //列名反驼峰处理，即：accountName -> ACCOUNT_NAME
        if (!DataUnitUtils.isAcronym(fieldName)) {
            fieldName = DataUnitUtils.deCamelCase(fieldName);
        }

        String tableId = dataUnitsDao.getTableIdByModelId(datasetId);
        DataFromVo dataFromVo = dataSourceDao.getConnIdByTableId(tableId);
        //changeDataSourceByTableId(tableId);

        fieldName = fieldName.toLowerCase(Locale.ROOT);
        String sql = "SELECT DISTINCT " + fieldName + " FROM " + dataFromVo.getSrcTable().toUpperCase(Locale.ROOT)
                + " WHERE " + fieldName + " LIKE '%" + keyword + "%'";
        //更换数据源
        //  new DataSourceManager().setCurrentDataSource(dataFromVo.getConnId());
        // List<Map<String, Object>> lists = dataUnitsDao.getIntColumnInfo2(sql);
        List<Map<String, Object>> lists = dbLinkUtils.getSqlResult(dataFromVo.getConnId(), sql);
        List<String> retList = new ArrayList<>();
        for (Map<String, Object> list : lists) {
            list.forEach((key, value) -> {
                if ((value instanceof String)) {
                    retList.add(value == null ? "" : (String) value);
                }
            });

        }
        // DataSourceManager.reset2defaultDataSource();
        HashMap<String, Object> retMap = new HashMap<>();
        retMap.put("value", retList);
        return retMap;
    }

    @Override
    public void insertOderTable(List<ColValueOrder> orders, DataNewFld dataNewFld, String type) {

        // 清理历史垃圾数据
        dataUnitsDao.deleteOrder(dataNewFld.getModelId(), dataNewFld.getColName(), type);

        if (orders != null && orders.size() != 0) {
            for (int i = 0; i < orders.size(); i++) {
                int seqNo = i + 1;
                ColValueOrder order = orders.get(i);
                TDataOrd dataOrd = new TDataOrd();
                dataOrd.setOrderId(order.getOrderId());
                dataOrd.setModelId(dataNewFld.getModelId());
                dataOrd.setColName(dataNewFld.getColName());
                dataOrd.setShowName(order.getShowName());
                dataOrd.setName(order.getColName());
                dataOrd.setType(type);
                dataOrd.setSeqNo("" + seqNo);
                if (order.getOrderId() == null || "".equals(order.getOrderId())) {
                    dataOrd.setOrderId(DataUnitUtils.uuid());
                    dataUnitsDao.insertOrderTable(dataOrd);
                } else {
                    dataUnitsDao.updateOrderTable(dataOrd);
                }
            }
        }
    }

    @Override
    public void insertTextGrp(List<TextGroup> textGroups, DataNewFld dnf) {
        // 清除文本历史数据
        if (dnf.getFieldId() != null || !"".equals(dnf.getFieldId())) {
            dataUnitsDao.deleteTextGrpByColumn(dnf.getModelId(), dnf.getFieldId());
        }
        for (int i = 0; i < textGroups.size(); i++) {
            TextGroup tgComing = textGroups.get(i);

            TTextGrp tTextGrp = new TTextGrp();
            tTextGrp.setId(dnf.getFieldId());
            tTextGrp.setModelId(dnf.getModelId());
            tTextGrp.setColName(tgComing.getColName());
            tTextGrp.setGroupName(tgComing.getGroupName());
            List<String> content = tgComing.getContent();
            for (int x = 0; x < content.size(); x++) {
                int seqNo = x + 1;
                String s = content.get(x);
                if (s != null && !"".equals(s)) {
                    tTextGrp.setValue(s);
                    tTextGrp.setSeqNo("" + seqNo);
                    dataUnitsDao.insertTextGrp(tTextGrp);
                }
            }
        }
    }

    @Override
    public void insertNumberGrp(List<NumberGroup> numberGroups, DataNewFld dnf) {
        // 清除文本历史数据
        if (dnf.getFieldId() != null || !"".equals(dnf.getFieldId())) {
            dataUnitsDao.deleteNumberGrpByFld(dnf.getFieldId());
        }
        if (numberGroups != null && numberGroups.size() > 0) {
            for (int i = 0; i < numberGroups.size(); i++) {
                int seqNo = i + 1;
                NumberGroup ngComing = numberGroups.get(i);

                TDataGrp tDataGrp = new TDataGrp();
                tDataGrp.setId(dnf.getFieldId());
                tDataGrp.setModelId(ngComing.getModelId());
                tDataGrp.setMin("" + ngComing.getMin());
                tDataGrp.setMax("" + ngComing.getMax());
                tDataGrp.setCloseMin(ngComing.getCloseMin() == "true" ? "1" : "0");
                tDataGrp.setCloseMax(ngComing.getCloseMax() == "true" ? "1" : "0");
                tDataGrp.setGroupName(ngComing.getGroupName());
                tDataGrp.setValid(ngComing.getValid());
                tDataGrp.setSeqNo("" + seqNo);
                dataUnitsDao.insertNumberGrp(tDataGrp);
            }

        }
    }

    @Override
    public void insertFunc() {
        dataUnitsDao.insertFun();
    }

    @Override
    public List<TDataNewfun> getAllTDataFunc() {
        return dataUnitsDao.getTDataFun();
    }

    @Override
    public DataFromVo getDataFromByModelId(String modelId) {
        // DataSourceManager.reset2defaultDataSource();
        String tableId = getTableIdByModelId(modelId);
        // 根据 tableId 从数据表来源表中查询数据源
        DataFromVo dataFromVo = dataSourceDao.getConnIdByTableId(tableId);
        return dataFromVo;
    }

    @Override
    public boolean checkFuncValid(String funcBody, String modelId) {

        //查找表名
        DataFromVo dataFromVo = getDataFromByModelId(modelId);
        String srcTable = dataFromVo.getSrcTable();
        String connId = dataFromVo.getConnId();
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ")
                .append(funcBody)
                .append(" FROM ")
                .append(srcTable);

        // new DataSourceManager().setCurrentDataSource(connId);
        try {
            //List<Map<String, Object>> list = dataUnitsDao.generalDataQuery(sb.toString());
            List<Map<String, Object>> list = dbLinkUtils.getSqlResult(dataFromVo.getConnId(), sb.toString());
            log.info("checkFuncValid:{}", list.toString());
        } catch (Exception e) {
            log.error(e.toString());
            return false;
        }
        // DataSourceManager.reset2defaultDataSource();
        return true;
    }

    @Override
    public List<DataNewFld> getNewFieldsByModelId(String modelId) {
        List<DataNewFld> newFldList = dataUnitsDao.getNewFieldsByModelId(modelId);
        return newFldList;
    }

    @Override
    public DataNewFld queryAddColumnById(String colId) {
        DataNewFld tDataFldPo = dataUnitsDao.getNewFieldsById(colId);
        return tDataFldPo;
    }

    @Override
    public void updateUniteBaseModelId(String unitId, String modelId) {
        dataUnitsDao.updateUnitBaseModelId(unitId, modelId);
    }

    @Override
    public List<TDataOrd> getOrderByType(String modelId, String colName, String type) {
        List<TDataOrd> result = dataUnitsDao.getOrderByType(modelId, colName, type);
        return result;
    }

    @Override
    public List<TTextGrp> getTextGrpByModelId(TTextGrp record) {
        return dataUnitsDao.getTextGrpByModelId(record);
    }

    @Override
    public List<TDataGrp> getDataGrpByModelId(NumberGroup dataGrp) {
        return dataUnitsDao.getDataGrpByModelId(dataGrp);
    }

    @Override
    public void deleteAddedColumnById(String fieldId) throws Exception {
        DataNewFld newFld = dataUnitsDao.selectAddedColumnById(fieldId);
        String type = newFld.getType() == null ? "" : newFld.getType();
        String modelId = newFld.getModelId();
        switch (type) {
            case "1":
            case "2":
            case "3":
            case "4":
            case "5":
            case "6":
                String value1 = newFld.getValue1() == null ? "" : newFld.getValue1();
                if ("2".equals(value1)) {
                    dataUnitsDao.deleteOrderByType(modelId, "1");
                    dataUnitsDao.deleteOrderByType(modelId, "2");
                }
                break;
            case "7":
            case "8":
            case "9":
                dataUnitsDao.deleteTextGrp(fieldId);
                dataUnitsDao.deleteNumberGrp(modelId);
                break;
        }
        dataUnitsDao.deleteAddedColumnById(fieldId);
    }

    @Override
    public List<DataNewFld> queryAllNewFldByModelId(String modelId) {
        return dataUnitsDao.queryAllNewFldByModelId(modelId);
    }

    private static Pattern humpPattern = Pattern.compile("[A-Z]");

    /**
     *  驼峰转下划线
     * @param str
     * @return
     */
    public static String humpToLine(String str) {
        Matcher matcher = humpPattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
        }
        matcher.appendTail(sb);
        String substring = sb.toString();
        return substring;
    }

}
